const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

console.log('🔍 分析87个英文实例表对应的属性表组合');
console.log('===========================================');

try {
  const db = new Database(DB_PATH);
  
  console.log('\n📋 获取当前英文实例表列表...');
  
  // 获取所有英文实例表
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📊 当前英文实例表总数: ${instanceTables.length} 个`);
  
  // 获取设备类型映射
  console.log('\n🔍 分析设备类型映射...');
  
  const deviceTypeMappings = db.prepare(`
    SELECT 
      classification_code,
      classification_name,
      english_name,
      specialty,
      naming_table,
      position_table,
      base_params_table
    FROM device_type_mappings
    ORDER BY classification_code
  `).all();
  
  console.log(`📊 设备类型映射总数: ${deviceTypeMappings.length} 个`);
  
  // 分析每个实例表对应的属性表组合
  console.log('\n🔍 分析实例表与属性表的对应关系...');
  
  const tableAttributeMappings = [];
  
  for (const table of instanceTables) {
    const tableName = table.name;
    
    // 从表名推断设备类型（去掉 device_instances_ 前缀）
    const deviceTypeFromTable = tableName.replace('device_instances_', '');
    
    // 查找匹配的设备类型映射
    let bestMatch = null;
    let matchScore = 0;
    
    for (const mapping of deviceTypeMappings) {
      // 计算匹配度（基于英文名称的相似性）
      const englishName = mapping.english_name ? mapping.english_name.toLowerCase() : '';
      const tableParts = deviceTypeFromTable.toLowerCase().split('_');
      
      let score = 0;
      if (englishName) {
        for (const part of tableParts) {
          if (englishName.includes(part) || part.includes(englishName.split('_')[0])) {
            score++;
          }
        }
      }
      
      // 直接匹配英文名称
      if (englishName === deviceTypeFromTable.toLowerCase()) {
        score += 10; // 完全匹配给高分
      }
      
      if (score > matchScore) {
        matchScore = score;
        bestMatch = mapping;
      }
    }
    
    tableAttributeMappings.push({
      instanceTable: tableName,
      deviceType: deviceTypeFromTable,
      mapping: bestMatch,
      matchScore: matchScore
    });
  }
  
  // 显示分析结果
  console.log('\n📊 实例表属性映射分析结果:');
  console.log('=====================================');
  
  const grouped = {};
  
  for (const mapping of tableAttributeMappings) {
    const key = mapping.mapping ? 
      `${mapping.mapping.naming_table}|${mapping.mapping.position_table}|${mapping.mapping.base_params_table}` : 
      'no_mapping';
    
    if (!grouped[key]) {
      grouped[key] = [];
    }
    grouped[key].push(mapping);
  }
  
  console.log(`\n🔍 按属性表组合分组 (${Object.keys(grouped).length} 种组合):`);
  
  Object.entries(grouped).forEach(([key, tables], index) => {
    console.log(`\n--- 组合 ${index + 1} ---`);
    
    if (key === 'no_mapping') {
      console.log('❌ 未找到匹配的设备类型映射');
      console.log(`📋 实例表数量: ${tables.length} 个`);
      tables.forEach(t => {
        console.log(`  - ${t.instanceTable} (设备类型: ${t.deviceType})`);
      });
    } else {
      const [namingTable, positionTable, baseParamsTable] = key.split('|');
      console.log(`📋 命名表: ${namingTable}`);
      console.log(`📋 位置表: ${positionTable}`);
      console.log(`📋 参数表: ${baseParamsTable}`);
      console.log(`📊 实例表数量: ${tables.length} 个`);
      
      // 只显示前5个，如果超过5个则省略
      const displayTables = tables.slice(0, 5);
      displayTables.forEach(t => {
        console.log(`  - ${t.instanceTable} (匹配度: ${t.matchScore})`);
      });
      
      if (tables.length > 5) {
        console.log(`  ... 还有 ${tables.length - 5} 个表`);
      }
    }
  });
  
  // 分析三个属性表的字段结构
  console.log('\n🔍 分析三个核心属性表的字段结构...');
  
  const coreAttributeTables = ['table_c2', 'table_c4', 'table_c6'];
  const tableFields = {};
  
  for (const tableName of coreAttributeTables) {
    try {
      const fields = db.prepare(`PRAGMA table_info([${tableName}])`).all();
      tableFields[tableName] = fields.filter(f => 
        !['id', 'created_at', 'updated_at'].includes(f.name)
      ).map(f => f.name);
      
      console.log(`\n📋 ${tableName} 字段 (${tableFields[tableName].length} 个):`);
      tableFields[tableName].forEach(field => {
        console.log(`  - ${field}`);
      });
    } catch (error) {
      console.log(`❌ 无法获取 ${tableName} 字段信息: ${error.message}`);
      tableFields[tableName] = [];
    }
  }
  
  // 为每种组合生成前缀命名的字段列表
  console.log('\n🔧 生成前缀命名字段组合...');
  
  const prefixMappings = {
    'table_c2': 'naming_',
    'table_c4': 'position_',
    'table_c6': 'position_',  // table_c6也是位置信息，使用相同前缀
  };
  
  // 生成每种属性表组合的完整字段列表
  const attributeCombinations = {};
  
  Object.entries(grouped).forEach(([key, tables]) => {
    if (key !== 'no_mapping') {
      const [namingTable, positionTable, baseParamsTable] = key.split('|');
      
      const allFields = [];
      
      // 添加命名字段
      if (tableFields[namingTable]) {
        tableFields[namingTable].forEach(field => {
          allFields.push(`naming_${field}`);
        });
      }
      
      // 添加位置字段
      if (tableFields[positionTable]) {
        tableFields[positionTable].forEach(field => {
          allFields.push(`position_${field}`);
        });
      }
      
      // 添加基础参数字段（需要查询对应的table_c*表）
      if (baseParamsTable && baseParamsTable !== 'null') {
        try {
          const baseFields = db.prepare(`PRAGMA table_info([${baseParamsTable}])`).all();
          const baseFieldNames = baseFields.filter(f => 
            !['id', 'created_at', 'updated_at'].includes(f.name)
          ).map(f => f.name);
          
          baseFieldNames.forEach(field => {
            allFields.push(`base_params_${field}`);
          });
        } catch (error) {
          console.log(`⚠️  无法获取 ${baseParamsTable} 字段: ${error.message}`);
        }
      }
      
      attributeCombinations[key] = {
        tables: tables,
        fields: allFields,
        namingTable,
        positionTable,
        baseParamsTable
      };
    }
  });
  
  console.log('\n📊 属性表组合字段统计:');
  Object.entries(attributeCombinations).forEach(([key, combo], index) => {
    console.log(`\n组合 ${index + 1}: ${combo.tables.length} 个实例表`);
    console.log(`  命名表: ${combo.namingTable} -> ${combo.fields.filter(f => f.startsWith('naming_')).length} 个字段`);
    console.log(`  位置表: ${combo.positionTable} -> ${combo.fields.filter(f => f.startsWith('position_')).length} 个字段`);
    console.log(`  参数表: ${combo.baseParamsTable} -> ${combo.fields.filter(f => f.startsWith('base_params_')).length} 个字段`);
    console.log(`  总字段数: ${combo.fields.length} 个`);
  });
  
  // 保存分析结果到文件
  const analysisResult = {
    totalInstanceTables: instanceTables.length,
    attributeCombinations: attributeCombinations,
    tableAttributeMappings: tableAttributeMappings,
    coreTableFields: tableFields,
    timestamp: new Date().toISOString()
  };
  
  const fs = require('fs');
  const outputPath = path.join(process.cwd(), 'scripts', 'instance-table-analysis-result.json');
  fs.writeFileSync(outputPath, JSON.stringify(analysisResult, null, 2));
  
  console.log(`\n💾 分析结果已保存到: ${outputPath}`);
  console.log('\n🎉 实例表属性映射分析完成！');
  
  console.log('\n📋 下一步操作建议:');
  console.log('1. 检查分析结果，确认属性表组合的准确性');
  console.log('2. 为每个实例表生成具体的字段结构更新脚本');
  console.log('3. 执行87个实例表的结构更新');
  
  db.close();
  
} catch (error) {
  console.error('❌ 分析失败:', error.message);
  console.error(error.stack);
}